package com.zy.smartdb.controller;

import com.zy.smartdb.pojo.*;
import com.zy.smartdb.util.CollectionUtil;
import com.zy.smartdb.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description:
 * @Author: zara
 * @Version V3.0
 * @Date: 2022/9/21 2:48 下午
 */
@RestController
@RequestMapping("dml")
@Slf4j
public class DmlController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @PostMapping("/list")
    public AjaxResult list(@RequestBody FindParam findParam) throws Exception {
//        String sql = "select * from user";
        findParam = (FindParam) StringUtil.safetyClone(findParam);
        String sql = buildSelectSql(findParam);
        List<Map<String, Object>> userListMap = jdbcTemplate.queryForList(sql);
        return AjaxResult.success(userListMap);
    }

    //todo
    @PostMapping("/save")
    public AjaxResult save(@RequestBody SaveParam saveParam) throws Exception {
        saveParam = (SaveParam) StringUtil.safetyClone(saveParam);
        String sql = buildSaveSql(saveParam);
        jdbcTemplate.execute(sql);
        return AjaxResult.success();
    }

    @PostMapping("/delete")
    public AjaxResult delete(@RequestBody DeleteParam deleteParam) throws Exception {
//        ids = fillSpace(ids);
//        table = fillSpace(table);
        deleteParam = (DeleteParam) StringUtil.safetyClone(deleteParam);
        String sql = buildDeleteSql(deleteParam);
        jdbcTemplate.execute(sql);
        return AjaxResult.success();
    }


    @PostMapping("/getTableInfo")
    private Map<String, FieldInfo> getTableInfo(String tableName) {
        String sql = "select * from " + tableName + "  limit 0";
        ResultSet tabs;

        Map<String, FieldInfo> fieldInfos = new HashMap<>();
        try {
            Connection connection = this.jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData dbMetaData = connection.getMetaData();
//            String[] types = {"TABLE"};
//            tabs = dbMetaData.getTables(connection.getCatalog(), "root", tableName, types);
//            tabs = dbMetaData.getTables(connection.getCatalog(), connection.getCatalog(), tableName, types);
//            while (tabs.next()) {
//                String tableName1 = tabs.getString("TABLE_NAME");
//                String tableSchema = tabs.getString("TABLE_SCHEM");
            ResultSet resultSet = dbMetaData.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
            while (resultSet.next()) {
                String name = resultSet.getString("COLUMN_NAME");
                String type = resultSet.getString("TYPE_NAME");
                String colRemarks = resultSet.getString("REMARKS");
                int size = resultSet.getInt("COLUMN_SIZE");
                fieldInfos.put(name, new FieldInfo(name, type, colRemarks, size));
            }
//            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return fieldInfos;
    }

    private String buildDeleteSql(DeleteParam deleteParam) {
        String sql = "delete from {0} where id in ({1})";
        sql = sql.replace("{0}", deleteParam.getTable())
                .replace("{1}", deleteParam.getIds());
        return sql;

    }

    private String buildSaveSql(SaveParam saveParam) {

        Map<String, Object> row = saveParam.getRow();

        Map<String, FieldInfo> tableInfoMap = getTableInfo(saveParam.getTable());

        Object id = row.get("id");
        if (StringUtil.isNullOrEmpty(id)) {
            String sql = "insert into {0} ({1}) values ({2})";
            List<String> keys = row.keySet().stream().filter(e -> !e.equals("id")).collect(Collectors.toList());
            String columns = CollectionUtil.join(keys);

            StringBuilder sb = new StringBuilder(keys.size() * 2);
            for (String key : keys) {
                Object value = row.get(key);
                FieldInfo fieldInfo = tableInfoMap.get(key);
                if (fieldInfo == null || fieldInfo.getType().equals("VARCHAR")) {
                    value = "'" + value + "'";
                }
                sb.append(value).append(',');
            }
            String values = sb.substring(0, sb.length() - 1);
            sql = sql.replace("{0}", saveParam.getTable())
                    .replace("{1}", columns)
                    .replace("{2}", values);
            return sql;
        } else {

            String sql = "update {0} set {1} where id={2}";

            StringBuilder sb = new StringBuilder();
            for (String key : row.keySet()) {
                if (key.equals("id")) continue;
                Object value = row.get(key);
                FieldInfo fieldInfo = tableInfoMap.get(key);
                if (fieldInfo == null || fieldInfo.getType().equals("VARCHAR")) {
                    value = "'" + value + "'";
                }
                sb.append(key).append("=").append(value).append(',');
            }

            String keyValues = sb.substring(0, sb.length() - 1);
            sql = sql.replace("{0}", saveParam.getTable())
                    .replace("{1}", keyValues)
                    .replace("{2}", row.get("id").toString());
            return sql;

        }

    }

    private String buildSelectSql(FindParam findParam) {
        String fields = "*";
        findParam = safetyHandle(findParam);
        List<String> fieldList = findParam.getFields();
        if (!CollectionUtil.isNullOrEmpty(fieldList)) {
            fields = CollectionUtil.join(fieldList);
        }
        String wheres = buildWheres(findParam);

        String sql = "select {0} from {1} where {2}";
        sql = sql.replace("{0}", fields)
                .replace("{1}", findParam.getTable())
                .replace("{2}", wheres);
        log.debug(sql);
        return sql;
    }

    private FindParam safetyHandle(FindParam findParam) {
        FindParam newFind = new FindParam();
        newFind.setTable(fillSpace(findParam.getTable()));
        List<String> fields = findParam.getFields();
        List<String> flist = new ArrayList<>(fields.size());
        for (String field : fields) {
            flist.add(fillSpace(field));
        }

        List<Where> wheres = findParam.getWheres();
        List<Where> wlist = new ArrayList<>(wheres.size());
        for (Where where : wheres) {
            Where wh = new Where();
            wh.setField(fillSpace(where.getField()));
            wh.setValue(fillSpace(where.getValue()));
            wh.setRelation(where.getRelation());
            wlist.add(wh);
        }

        newFind.setFields(flist);
        newFind.setWheres(wlist);

        return newFind;

    }

    //todo
    public static String fillSpace(String str) {
        return str;
    }


    private String buildWheres(FindParam findParam) {
        List<Where> whereList = findParam.getWheres();
        Map<String, FieldInfo> tableInfoMap = getTableInfo(findParam.getTable());
        StringBuilder sb = new StringBuilder();
        sb.append(" 1 = 1 ");
        for (Where where : whereList) {
            String field = where.getField();
            sb.append(" and ");
            FieldInfo fieldInfo = tableInfoMap.get(field);
            String value = where.getValue();
            if (fieldInfo == null || fieldInfo.getType().equals("VARCHAR")) {
                value = "'" + value + "'";
            }
            switch (where.getRelation()) {
                case EQ:
                    sb.append(field).append(" = ").append(value);
                    break;
                case LT:
                    sb.append(field).append(" < ").append(value);
                    break;
                case GT:
                    sb.append(field).append(" > ").append(value);
                    break;
                case LIKE:
                    sb.append(field).append(" like ").append(value);
                    break;
                case ISNULL:
                    sb.append(field).append(" is null ");
                    break;

            }
        }
        return sb.toString();
    }

}

